package apj.demo.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import apj.demo.dao.IncomeDao;
import apj.demo.model.Income;
import apj.demo.util.JDBCUtil;

public class IncomeDaoImpl implements IncomeDao {
	private final String LIMIT5 = "select * from income limit ?,5";
	private final String FIND_BY_ALL = "select * from income";
	private final String FIND_BY_ID = "select * from income where id = ?";
	private final String SAVE = "insert income "
			+ "(id,daily_Income,employee,department,bussiness_date,last_mod_date) "
			+ "values(?,?,?,?,?,?)";
	private final String UPDATE = "update income set "
			+ "daily_Income=?,employee=?,department=?,bussiness_date=?,last_mod_date=? where id=?";

	/*获取单个页面账单数据 5条*/
	public List<Income> pageData(int index) throws SQLException{
		ArrayList<Income> incomes = new ArrayList<Income>();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			Income income = null;
			conn = JDBCUtil.getConnection();
			ps = conn.prepareStatement(LIMIT5);
			index = (index*5)-5;
			ps.setInt(1, index);
			rs = ps.executeQuery();
			while(rs.next()){
				income = new Income();
				income.setId(rs.getInt("id"));
				income.setDaily(rs.getDouble("daily_Income"));
				income.setEmployee(rs.getString("employee"));
				income.setDepartment(rs.getString("department"));
				income.setBussiness(rs.getDate("bussiness_date"));
				income.setLastMod(rs.getDate("last_mod_date"));
				incomes.add(income);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			JDBCUtil.close(rs);
			JDBCUtil.close(ps);
			JDBCUtil.close(conn);
		}
		return incomes;
	}

	/*根据ID来获取一个账单数据*/
	public Income findById(int id) throws SQLException {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		Income income = new Income();
		try{
			conn = JDBCUtil.getConnection();
			ps = conn.prepareStatement(FIND_BY_ID);
			ps.setInt(1, id);
			rs = ps.executeQuery();
			while(rs.next()){
				income.setId(rs.getInt("id"));
				income.setDaily(rs.getDouble("daily_Income"));
				income.setEmployee(rs.getString("employee"));
				income.setDepartment(rs.getString("department"));
				income.setBussiness(rs.getDate("bussiness_date"));
				income.setLastMod(rs.getDate("last_mod_date"));
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			JDBCUtil.close(rs);
			JDBCUtil.close(ps);
			JDBCUtil.close(conn);
		}
		return income;
	}

	/*保存账单*/
	public void save(Income income) throws SQLException {
		Connection conn = null;
		PreparedStatement ps = null;
		try{
			conn = JDBCUtil.getConnection();
			ps = conn.prepareStatement(SAVE);
				ps.setInt(1, income.getId());
				ps.setDouble(2,income.getDaily());
				ps.setString(3,income.getEmployee());
				ps.setString(4,income.getDepartment());
				ps.setDate(5,income.getBussiness());
				ps.setDate(6,income.getLastMod());
				ps.executeUpdate();
			
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			JDBCUtil.close(ps);
			JDBCUtil.close(conn);
		}
	}

	/*更新账单数据*/
	public void update(Income income) throws SQLException {
		Connection conn = null;
		PreparedStatement ps = null;
		try{
			conn = JDBCUtil.getConnection();
			ps = conn.prepareStatement(UPDATE);
				ps.setDouble(1,income.getDaily());
				ps.setString(2,income.getEmployee());
				ps.setString(3,income.getDepartment());
				ps.setDate(4,income.getBussiness());
				ps.setDate(5,income.getLastMod());
				ps.setInt(6, income.getId());
				ps.executeUpdate();
			
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			JDBCUtil.close(ps);
			JDBCUtil.close(conn);
		}
	}

	/*返回账单所有数据*/
	public List<Income> findByAll() throws SQLException {
		ArrayList<Income> incomes = new ArrayList<Income>();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			Income income = null;
			conn = JDBCUtil.getConnection();
			ps = conn.prepareStatement(FIND_BY_ALL);
			rs = ps.executeQuery();
			while(rs.next()){
				income = new Income();
				income.setId(rs.getInt("id"));
				income.setDaily(rs.getDouble("daily_Income"));
				income.setEmployee(rs.getString("employee"));
				income.setDepartment(rs.getString("department"));
				income.setBussiness(rs.getDate("bussiness_date"));
				income.setLastMod(rs.getDate("last_mod_date"));
				incomes.add(income);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			JDBCUtil.close(rs);
			JDBCUtil.close(ps);
			JDBCUtil.close(conn);
		}
		return incomes;
	}

}
